namespace SimpleWebServices.Data
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Globalization;
    using System.Net;
    using System.Reflection;
    using System.Web;
    using System.Xml;
    using System.Xml.XPath;
    using SimpleWebServices.Properties;
    using SimpleWebServices.Security.Cryptography;
    using SimpleWebServices.Web;

    public sealed class SqlClientRepository : IRepository
    {
        private string _connectionString;

        public SqlClientRepository()
            : this(Settings.Default.RepositoryConnectionString)
        {
        }

        public SqlClientRepository(string connectionString)
        {
            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository(connectionString={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    connectionString));

            this.ConnectionString = connectionString;
        }

        public string ConnectionString
        {
            get
            {
                return this._connectionString;
            }

            set
            {
                if (null == value)
                {
                    throw new ArgumentNullException("value");
                }
                else if (0 == value.Length)
                {
                    throw new ArgumentOutOfRangeException("value");
                }

                this._connectionString = value;
            }
        }

        bool IRepository.Delete(AbsoluteUri urn)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Delete(urn={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.delete.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        result = 1 == command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Delete(Token token)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Delete(token={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    token));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.delete.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);

                        result = 1 == command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Exists(AbsoluteUri urn)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Exists(urn={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.exists.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        result = Convert.ToBoolean(command.ExecuteScalar(), CultureInfo.InvariantCulture);
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Exists(Token token)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Exists(token={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    token));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.exists.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);

                        result = Convert.ToBoolean(command.ExecuteScalar(), CultureInfo.InvariantCulture);
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Exists(string xpath)
        {
            if (null == xpath)
            {
                throw new ArgumentNullException("xpath");
            }
            else if (0 == xpath.Length)
            {
                throw new ArgumentOutOfRangeException("xpath");
            }
            else if (xpath.Contains("']"))
            {
                throw new FormatException("Please use double quotes in the xpath expression.");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Exists(xpath={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    xpath));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.exists.xpath]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddNVarChar("xpath", xpath, 255);

                        result = Convert.ToBoolean(command.ExecuteScalar(), CultureInfo.InvariantCulture);
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        XmlDataObject IRepository.Export(AbsoluteUri urn)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Export(urn={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn));

            XmlDataObject result = null;

            try
            {
                DataSet data = new DataSet(urn);
                data.Locale = CultureInfo.InvariantCulture;

                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.select.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(data);
                        }
                    }

                    if (0 != data.Tables[0].Rows.Count)
                    {
                        result = Read(data.Tables[0].Rows[0]);
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        XmlDataObject IRepository.Export(Token token)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Export(token={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    token));

            XmlDataObject result = null;

            try
            {
                DataSet data = new DataSet(token);
                data.Locale = CultureInfo.InvariantCulture;

                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.select.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(data);
                        }
                    }

                    if (0 != data.Tables[0].Rows.Count)
                    {
                        result = Read(data.Tables[0].Rows[0]);
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        void IRepository.Insert(IRecord record)
        {
            if (null == record)
            {
                throw new ArgumentNullException("record");
            }
            else if (record.Token.HasValue)
            {
                throw new RepositoryException("The record already has a token.");
            }
            else if (null == record.Urn)
            {
                throw new RepositoryException("An record URN is required.");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Insert(record={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    record.Serialize().CreateNavigator().OuterXml));

            try
            {
                using (SqlCommand command = Command("[dbo].[object.insert]", record))
                {
                    using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                    {
                        command.Connection = connection;
                        command.Connection.Open();
                        record.Token = (Guid)command.ExecuteScalar();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }
        }

        bool IRepository.Match(AbsoluteUri urn, string etag)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }
            else if (null == etag)
            {
                throw new ArgumentNullException("etag");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Match(urn={1}, etag={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn,
                    etag));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.match.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);
                        command.Parameters.AddVarChar("etag", etag, 24);

                        result = (bool)command.ExecuteScalar();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Match(Token token, string etag)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }
            else if (null == etag)
            {
                throw new ArgumentNullException("etag");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Match(token={1}, etag={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    token,
                    etag));

            bool result = false;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.match.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);
                        command.Parameters.AddVarChar("etag", etag, 24);

                        result = (bool)command.ExecuteScalar();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.ModifiedSince(AbsoluteUri urn, DateTime value)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.ModifiedSince(urn={1}, value={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn,
                    value.ToXmlString()));

            bool result = true;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.modified.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        DateTime? modified = (DateTime?)command.ExecuteScalar();
                        if (modified.HasValue)
                        {
                            result = modified > value;
                        }
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.ModifiedSince(Token token, DateTime value)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.ModifiedSince(token={1}, value={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    token,
                    value.ToXmlString()));

            bool result = true;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.modified.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);

                        DateTime? modified = (DateTime?)command.ExecuteScalar();
                        if (modified.HasValue)
                        {
                            result = modified > value;
                        }
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        IList<T> IRepository.Query<T>(string xpath)
        {
            if (null == xpath)
            {
                throw new ArgumentNullException("xpath");
            }
            else if (0 == xpath.Length)
            {
                throw new ArgumentOutOfRangeException("xpath");
            }
            else if (xpath.Contains("']"))
            {
                throw new FormatException("Please use double quotes in the xpath expression.");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Query<{1}>(xpath={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    typeof(T).Name,
                    xpath));

            List<T> result = new List<T>();

            try
            {
                DataSet data = new DataSet(xpath);
                data.Locale = CultureInfo.InvariantCulture;

                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.select.xpath]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddNVarChar("xpath", xpath, 255);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(data);
                        }
                    }
                }

                foreach (DataRow row in data.Tables[0].Rows)
                {
                    result.Add(SqlClientRepository.Read<T>(row));
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        T IRepository.Select<T>(AbsoluteUri urn)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Select<{1}>(urn={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    typeof(T).Name,
                    urn));

            T result = default(T);

            try
            {
                DataSet data = new DataSet(urn);
                data.Locale = CultureInfo.InvariantCulture;

                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.select.urn]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(data);
                        }
                    }
                }

                if (0 != data.Tables[0].Rows.Count)
                {
                    result = SqlClientRepository.Read<T>(data.Tables[0].Rows[0]);
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        T IRepository.Select<T>(Token token)
        {
            if (token.IsEmpty)
            {
                throw new ArgumentOutOfRangeException("token");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Select<{1}>(token={2})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    typeof(T).Name,
                    token));

            T result = default(T);

            try
            {
                DataSet data = new DataSet(token);
                data.Locale = CultureInfo.InvariantCulture;

                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.select.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddToken("token", token);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(data);
                        }
                    }
                }

                if (0 != data.Tables[0].Rows.Count)
                {
                    result = SqlClientRepository.Read<T>(data.Tables[0].Rows[0]);
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        Token? IRepository.Token(AbsoluteUri urn)
        {
            if (null == urn)
            {
                throw new ArgumentNullException("urn");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Token(urn={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    urn));

            Token? result = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                {
                    connection.Open();

                    using (SqlCommand command = new SqlCommand("[dbo].[object.token]", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddVarChar("urn", urn, 260);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                result = reader.GetSqlGuid(0).Value;
                            }
                        }
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        bool IRepository.Update(IRecord record)
        {
            if (null == record)
            {
                throw new ArgumentNullException("record");
            }
            else if (!record.Token.HasValue)
            {
                throw new RepositoryException("An record token is required.");
            }
            else if (record.Token.Value.IsEmpty)
            {
                throw new RepositoryException("An empty record token is invalid.");
            }
            else if (null == record.Urn)
            {
                throw new RepositoryException("An record URN is required.");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Update(record={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    record.Serialize().CreateNavigator().OuterXml));

            bool result = false;

            try
            {
                using (SqlCommand command = SqlClientRepository.Command("[dbo].[object.update]", record))
                {
                    using (SqlConnection connection = new SqlConnection(this.ConnectionString))
                    {
                        command.Connection = connection;
                        command.Connection.Open();
                        result = 1 == command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException exception)
            {
                throw new RepositoryException(exception);
            }

            return result;
        }

        void IRepository.Upsert(IRecord record)
        {
            if (null == record)
            {
                throw new ArgumentNullException("record");
            }

            Debug.WriteLine(
                string.Format(
                    CultureInfo.InvariantCulture,
                    "{0} >> SqlClientRepository.Upsert(record={1})",
                    Assembly.GetExecutingAssembly().GetName().Name,
                    record.Serialize().CreateNavigator().OuterXml));

            if (record.Token.HasValue)
            {
                (this as IRepository).Update(record);
            }
            else
            {
                (this as IRepository).Insert(record);
            }
        }

        private static SqlCommand Command(string cmdText, IRecord record)
        {
            return SqlClientRepository.Command(
                cmdText, 
                record.Token, 
                record.Urn, 
                DateTimeFactory.UtcNow, 
                record.Status, 
                record.Cacheability, 
                MD5Hash.Compute(record.Entity), 
                record.Expires,
                record.GetType().Assembly.GetName().Name,
                record.GetType().FullName,
                record.Serialize());
        }

        private static SqlCommand Command(string cmdText, Guid? token, AbsoluteUri urn, DateTime modified, HttpStatusCode? status, HttpCacheability? cacheability, MD5Hash etag, Expiration expires, string assembly, string type, IXPathNavigable xml)
        {
            SqlCommand result = new SqlCommand(cmdText);
            result.CommandType = CommandType.StoredProcedure;

            result.Parameters.AddVarChar("assembly", assembly, 260);
            result.Parameters.AddVarChar("type", type, 260);

            result.Parameters.AddXml("xml", xml);
            if (token.HasValue && !Token.Empty.Equals(token.Value))
            {
                result.Parameters.AddToken("token", token.Value);
            }

            result.Parameters.AddVarChar("urn", urn, 260);

            result.Parameters.AddDateTime("modified", modified);
            if (status.HasValue)
            {
                result.Parameters.AddInt32("status", (int)status.Value);
            }

            if (cacheability.HasValue)
            {
                result.Parameters.AddInt32("cacheability", (int)cacheability.Value);
            }

            result.Parameters.AddChar("etag", etag, 24);
            if (!string.IsNullOrEmpty(expires))
            {
                result.Parameters.AddVarChar("expires", expires, 50);
            }

            return result;
        }

        private static T Read<T>(DataRow row)
            where T : IRecord
        {
            T result = row.Field<string>("xml").Deserialize<T>();

            result.Token = row.Field<Guid>("token");
            result.Urn = row.Field<string>("urn");

            result.Created = row.Field<DateTime>("created");
            result.Modified = row.Field<DateTime>("modified");

            result.Status = row.Field<HttpStatusCode>("status");
            result.Cacheability = row.Field<HttpCacheability>("cacheability");
            result.Etag = row.Field<string>("etag");
            result.Expires = row.Field<string>("expires");

            return result;
        }

        private static XmlDataObject Read(DataRow row)
        {
            XmlDataObject result = new XmlDataObject();

            result.ObjectType = Type.GetType(row.Field<string>("type"));

            XmlDocument xml = new XmlDocument();
            xml.LoadXml(row.Field<string>("xml"));
            result.Xml = xml;

            result.Token = row.Field<Guid>("token");
            result.Urn = row.Field<string>("urn");

            result.Created = row.Field<DateTime>("created");
            result.Modified = row.Field<DateTime>("modified");

            result.Status = row.Field<HttpStatusCode>("status");
            result.Cacheability = row.Field<HttpCacheability>("cacheability");
            result.Expires = row.Field<string>("expires");

            return result;
        }
    }
}